package dao;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.mysql.fabric.Server;
import com.mysql.fabric.xmlrpc.base.Data;

public class telcomdb {
	 //声明Connection对象
    Connection con;
    //驱动程序名
    String driver = "com.mysql.jdbc.Driver";
    //URL指向要访问的数据库名mydata
    String url = "jdbc:mysql://localhost:3306/telcom?useSSL=true";
    //MySQL配置时的用户名
    String user = "root";
    //MySQL配置时的密码
    String password = "mysql";
    
    //数据存储目录
    String savepath = "D:/workspace/HotEventMonitoring/2017年1月-2017年12月嘉兴的单子信息/save.txt";
    String fihcpath = "D:/workspace/HotEventMonitoring/2017年1月-2017年12月嘉兴的单子信息/fihcsave.txt";
	public void addToSql(List<String> datalist) throws ParseException, SQLException, ClassNotFoundException{
		 Class.forName(driver);
		PreparedStatement psql;
		con = DriverManager.getConnection(url,user,password);
		 if(!con.isClosed()){
			   System.out.println("已连接至数据库");
		 }
           
		psql = con.prepareStatement("insert into telcom (ID,CONTENT,TIME) "
		        + "values(?,?,?)");
		for(String data:datalist){
			String[] arrdata = data.split("\\+\\+");
			String id = arrdata[0];
			String content = arrdata[1];
			String time = arrdata[2].substring(0,10);
			DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date = dateFormat.parse(time);
			System.out.println(time+":"+date.toString());
			psql.setString(1, id);
			psql.setString(2, content);
			psql.setDate(3, new java.sql.Date (date.getTime()));
			psql.executeUpdate();
		}
	}
	
	public void getDataFromSql(String begintime,String endtime) throws ParseException, ClassNotFoundException, SQLException{
		Class.forName(driver);
		con = DriverManager.getConnection(url,user,password);
		 if(!con.isClosed()){
			   System.out.println("已连接至数据库");
		 }
		 List<String> outlist = new ArrayList<>();
		Statement statement = con.createStatement();
//		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-DD");
//		Date begin = dateFormat.parse(begintime);
//		Date end = dateFormat.parse(endtime);
		String sql = "select * from telcom where TIME between \'"+begintime+"\' AND \'"+endtime+"\'";
		ResultSet rs = statement.executeQuery(sql);
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		while(rs.next()){
			String id = rs.getString("ID");
			String content = rs.getString("CONTENT");
			String date =formatter.format(rs.getDate("TIME"));
			outlist.add(id+"++"+content+"++"+date);
		}
		 rs.close();
         con.close();
		IOHandler.serialize2File(outlist, new File(savepath));
	}
	
	public void getDataFromSqlforFIHC(String begintime,String endtime) throws ParseException, ClassNotFoundException, SQLException{
		Class.forName(driver);
		con = DriverManager.getConnection(url,user,password);
		 if(!con.isClosed()){
			   System.out.println("已连接至数据库");
		 }
		 List<String> outlist = new ArrayList<>();
		Statement statement = con.createStatement();
//		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-DD");
//		Date begin = dateFormat.parse(begintime);
//		Date end = dateFormat.parse(endtime);
		String sql = "select * from telcom where TIME between \'"+begintime+"\' AND \'"+endtime+"\'";
		ResultSet rs = statement.executeQuery(sql);
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		while(rs.next()){
			String id = rs.getString("ID");
			String content = rs.getString("CONTENT");
			String date =formatter.format(rs.getDate("TIME"));
			System.out.println(date);
			date = date.replaceAll("\\-", "\\/")+" 00:00:00";
			outlist.add(id+"|&|"+date+"|&|"+"嘉兴"+"|&|"+content);
		}
		 rs.close();
         con.close();
		IOHandler.serialize2File(outlist, new File(fihcpath));
	}
	    public static void main(String[] args) throws ParseException, SQLException, ClassNotFoundException {
	    	telcomdb telcomdb = new telcomdb();
	  
	    	/*String filepath = "D:/workspace/HotEventMonitoring/2017年1月-2017年12月嘉兴的单子信息/1-6月.txt";  
	    	List<String> datalist = IOHandler.readAsList(new File(filepath));
	    	telcomdb.addToSql(datalist);*/
	    	String begin = "2017-01-01";
	    	String end = "2017-02-03";
	    	telcomdb.getDataFromSql(begin, end);
	    }

	}